package com.xiaoma.verbal.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.collections.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import com.xiaoma.tpo.utils.Pager;
import com.xiaoma.verbal.baseDao.DefaultRowMapper;
import com.xiaoma.verbal.utils.Pagination;
import com.xiaoma.verbal.baseDao.BaseDaoImpl;
import com.xiaoma.verbal.domain.PrctPracticeAudio;
import com.xiaoma.verbal.dto.CorrectedAudioVo;
import com.xiaoma.verbal.dto.QuTpoVo;

@Repository
public class PrctPracticeAudioDao extends BaseDaoImpl<PrctPracticeAudio> {
	private static final Logger LOG = LoggerFactory.getLogger(PrctPracticeAudioDao.class);

//	public Pagination queryPageList(Integer userId, String questionNum, Integer status, Integer pageNo, Integer pageSize) {
//
//		List<Object> params = new ArrayList<Object>();
//		String sqlQuery = " ";
//		String sqlCount = " ";
//		sqlQuery = " SELECT a.*,b.delivery_score,b.language_score,b.topic_score,b.comprehensive_score,b.comment,b.finish_time,c.content,c.id as question_id  ";
//		String sqlTrunk = " FROM prct_practice_audio a left join prct_score_record b on a.id = b.practice_id  left join qu_question c on a.question_num = c.question_num ";
//		String where = " where a.status > 0 ";
//		String orderBy = " order by a.id desc ";
//		String limit = " limit ?,? ";
//		if (userId != null && !"".equals(userId)) {
//			where = where + " and a.user_id = ? ";
//			params.add(userId);
//		}
//		if (questionNum != null && !"".equals(questionNum)) {
//			where = where + " and a.question_num = upper(?) ";
//			params.add(questionNum.toUpperCase());
//		}
//		if (status != null && !"".equals(status)) {
//			where = where + " and a.status = ? ";
//			params.add(status);
//		}
//
//		sqlQuery = sqlQuery + sqlTrunk + where + orderBy + limit;
//		sqlCount = "select count(*) " + sqlTrunk + where;
//		LOG.info("SQL:{} PARAMS:{}", sqlQuery, params);
//		LOG.info(sqlCount, pageNo, pageSize);
//		int totalCount = jdbcTemplate.queryForInt(sqlCount, params.toArray());
//		Pagination pager = new Pagination(pageNo, pageSize, totalCount);
//		params.add(pager.getFirstResult());
//		params.add(pager.getPageSize());
//		List<?> list = jdbcTemplate.query(sqlQuery, params.toArray(), new DefaultRowMapper(CorrectedAudioVo.class, this.getNameHandler()));
//		pager.setList(list);
//		return pager;
//
//		// TODO Auto-generated method stub
//	}
	public Pagination queryPageList(Integer userId, String questionNum, Integer status, Integer pageNo, Integer pageSize) {

		List<Object> params = new ArrayList<Object>();
		String sqlQuery = " ";
		String sqlCount = " ";
		sqlQuery = " SELECT a.*,b.delivery_score,b.language_score,b.topic_score,"
				+ " b.comprehensive_score,b.comment,b.finish_time,c.content,c.id as question_id ";
		String sqlTrunk = " FROM prct_practice_audio a "
				+ " left join prct_score_record b on a.id = b.practice_id  "
				+ " left join qu_question c on a.question_num = c.question_num ";
		String where = " where a.status > 0 ";
		String orderBy = " order by a.create_time DESC ";
		String limit = " limit ?,? ";
		if (userId != null && !"".equals(userId)) {
			where = where + " and a.user_id = ? ";
			params.add(userId);
		}
		if (questionNum != null && !"".equals(questionNum)) {
			where = where + " and a.question_num = upper(?) ";
			params.add(questionNum.toUpperCase());
		}
		if (status != null && !"".equals(status)) {
			where = where + " and a.status = ? ";
			params.add(status);
		}

		sqlQuery = sqlQuery + sqlTrunk + where + orderBy + limit;
		sqlCount = "select count(*) " + sqlTrunk + where;
		LOG.info("SQL:{} PARAMS:{}", sqlQuery, params);
		LOG.info(sqlCount, pageNo, pageSize);
		int totalCount = jdbcTemplate.queryForInt(sqlCount, params.toArray());
		Pagination pager = new Pagination(pageNo, pageSize, totalCount);
		if(pageNo != 0 && pageNo >0){
			params.add((pageNo-1)*pageSize);
		}else{
			params.add(0);
		}
		params.add(pager.getPageSize());
		List<?> list = jdbcTemplate.query(sqlQuery, params.toArray(), new DefaultRowMapper(CorrectedAudioVo.class, this.getNameHandler()));
		pager.setList(list);
		return pager;

		// TODO Auto-generated method stub
	}

	public CorrectedAudioVo getByUuid(Integer userId, String uuid) {
		String sql = " SELECT a.*,b.delivery_score,b.language_score,b.topic_score,b.comprehensive_score,b.comment,b.finish_time FROM prct_practice_audio a left join prct_score_record b on a.id = b.practice_id WHERE a.uuid = ? and a.user_id = ?";
		LOG.info("SQL:{} PARAMS:{}", sql);
		List<?> result = jdbcTemplate.query(sql, new DefaultRowMapper(CorrectedAudioVo.class, getNameHandler()), uuid, userId);
		if (!CollectionUtils.isEmpty(result)) {
			return (CorrectedAudioVo) result.get(0);
		}
		return null;
	}
	//新的
	public CorrectedAudioVo getByUuidNew(Integer userId, String uuid) {
		String sql = " SELECT a.*,b.delivery_score,b.language_score,b.topic_score,b.comprehensive_score,b.comment,b.finish_time "
				+ " FROM prct_practice_audio a "
				+ " left join prct_score_record b "
				+ " on a.id = b.practice_id "
				+ " WHERE a.uuid = ? and a.user_id = ?";
		LOG.info("SQL:{} PARAMS:{}", sql);
		List<?> result = jdbcTemplate.query(sql, new DefaultRowMapper(CorrectedAudioVo.class, getNameHandler()), uuid, userId);
		if (!CollectionUtils.isEmpty(result)) {
			return (CorrectedAudioVo) result.get(0);
		}
		return null;
	}

	public CorrectedAudioVo getVoById(Integer id) {
		String sql = "  SELECT a.*,b.delivery_score,b.language_score,b.topic_score,b.comprehensive_score,b.comment,b.finish_time,c.content,c.id as question_id  FROM prct_practice_audio a left join prct_score_record b on a.id = b.practice_id  left join qu_question c on a.question_num = c.question_num where a.id=?";
		LOG.info("SQL:{} PARAMS:{}", sql);
		List<?> result = jdbcTemplate.query(sql, new DefaultRowMapper(CorrectedAudioVo.class, getNameHandler()), id);
		if (!CollectionUtils.isEmpty(result)) {
			return (CorrectedAudioVo) result.get(0);
		}
		return null;
	}

	public Pager queryPageList(Integer startIndex, Integer itemsPerPage) {
		Pager pager = new Pager();
		List<Object> params = new ArrayList<Object>();
		String sqlQuery = " ";
		String sqlCount = " ";
		sqlQuery = " SELECT d.name as user_name,a.*,b.delivery_score,b.language_score,b.topic_score,b.comprehensive_score,b.comment,b.finish_time,c.content,c.id as question_id  ";
		String sqlTrunk = " FROM prct_practice_audio a left join prct_score_record b on a.id = b.practice_id  left join qu_question c on a.question_num = c.question_num left join user d on a.user_id = d.id ";
		String where = " where a.status > 0 ";
		String orderBy = " order by a.id desc ";
		String limit = " limit ?,? ";
		sqlQuery = sqlQuery + sqlTrunk + where + orderBy + limit;
		sqlCount = "select count(*)  " + sqlTrunk + where;
		LOG.info(sqlQuery + " PARAMS: " + startIndex + ", " + itemsPerPage);
		LOG.info(sqlCount);
		int totalCount = jdbcTemplate.queryForInt(sqlCount, params.toArray());

		params.add(startIndex);
		params.add(itemsPerPage);
		List<?> list = jdbcTemplate.query(sqlQuery, params.toArray(), new DefaultRowMapper(CorrectedAudioVo.class, this.getNameHandler()));

		pager.setList(list);
		pager.setTotalCount(totalCount);
		return pager;
	}

	/**
	 *新 
	 */
	//新
	public  boolean findbyClsIdAndQuestionNumAndUserId(
			Integer clsClassId, String questionNum, Integer userId) {
		String sql = "select count(id)"
				+ " from prct_practice_audio"
				+ " where cls_class_id =? and question_num=? and user_id=?";
		Integer count = jdbcTemplate.queryForInt(sql,clsClassId,questionNum,userId);
		if(count > 0){
			return true;
		}
		return false;
	}


}
